--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_cote AS (SELECT * FROM &erp.cli_cote WHERE 1=2);




DELETE FROM se_cli_cote;

 INSERT INTO se_cli_cote
 (SELECT id cote_cod,
         0 enreg_id,
         ''cotefam_cod,
         'N' enreg_s,
         libelle cote_lib,
         NULL cote_lib2,
         Nvl(bloque,'N') bloque_ok,
         'O' externe_ok
    FROM UV_CCOTESCRL  ) ;

 UPDATE se_cli_cote SET cote_cod=REPLACE(cote_cod,'.',',');
 UPDATE se_cli_cote SET cote_cod=REPLACE(cote_cod,'+','P');
 UPDATE se_cli_cote SET cote_cod=REPLACE(cote_cod,'-','M');


 UPDATE se_cli_cote tf SET enreg_id=(select enreg_id FROM &erp.cli_cote f WHERE f.cote_cod=tf.cote_cod) ;
 UPDATE se_cli_cote tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('cote_cod','CLI_COTE','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,cote_cod, enreg_id, cotefam_cod, enreg_s, cote_lib, cote_lib2, bloque_ok, externe_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_cli_cote g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.cli_cote g
  )
GROUP BY cote_cod, enreg_id, cotefam_cod, enreg_s, cote_lib, cote_lib2, bloque_ok, externe_ok
HAVING Count(1)!=2) r
ORDER BY cote_cod;